package com.jse.jdbc;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

import javax.sql.DataSource;
import com.jse.Casts;
import com.jse.Io;
import com.jse.Ioc;
import com.jse.Lang;
import com.jse.Strings;
import com.jse.anno.Table;
import com.jse.json.Json;
import com.jse.json.JsonObject;

@SuppressWarnings({"rawtypes","unchecked"})
public class JseDao {
	
	public DataSource dataSource() {return Ioc.get("dataSource");}
	public Connection getConnection(){return Jdbc.getConnection(dataSource());}
	
	public List<JsonObject> query(String table){
		return Sql.create(table).queryList(JsonObject.class);
	}
	public List<JsonObject> query(String table,Sql where){
		return query(table, where,null);
	}
	public List<JsonObject> query(String table,Sql where,Map p){
		return null;
	}
	public List<JsonObject> query(String table,Object where,int pageNo,int pageSize)throws SQLException{
		return null;
	}
	
	
	public Map pager(String table,Object where,int page,int limit){return pager(table, where,Lang.ofMap("pageNo",page,"pageSize",limit));}
	public Map pager(String table,Object where,Map p){
		
		return null;
	}
	
	public Object query(Sql sql) {
		return null;
	}
	
	
	
	public Map<String,Object> fetch(String table,Object where){
		return null;
	}
	
	public Object select(String sql) throws SQLException {
		return null;
	}
	public Object select(String sql,String type,Object...args) throws SQLException {
		return null;
	}
	
	public List<JsonObject> queryForList(String sql,Object...args){
		return null;
	}
	public <T> List<T> queryForList(String sql,Class<T> cls,Object...args){
		return queryForList(cls,sql,args);
	}
	
	public <T> List<T> queryForList(Class<T> cls,String sql,Object...args){
		return null;
	}
	
	public JsonObject queryForMap(String sql,Object...args) {
		return null;
	}
	public <T> T queryForObject(String sql,Class<T> cls,Object...args){
		return queryForObject(cls, sql, args);}
	public <T> T queryForObject(Class<T> cls,String sql,Object...args){
		return null;
	}
	
	public Object save(Object o){return insert(o);}
	public Object insert(Object o){
		if(o instanceof Collection c)return insert(c);
		if(o.getClass().getName().endsWith(".ScriptObjectMirror")) {
			if(o.toString().equals("[object Array]"))return insert(((Map)o).values());
		}
		var m=toMap(o);
		String table=m.remove("table").toString();
		Object id=m.remove("+id");//id name
		var column=new StringJoiner(",");
		var values=new StringJoiner(",");
		m.forEach((k,v)->{
			column.add("`"+k+"`");values.add("?");
		});
		String sql="INSERT INTO %s (%s) VALUES (%s)".formatted(table,column,values);
		var conn=getConnection();
		try(var stmt=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)){
			var args=m.values().toArray();
			Jdbc.setParameters(stmt,args);
			if(Jdbc.showSql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("insert sql"+stmt.toString().substring(stmt.getClass().getName().length()));
			}
			var status=stmt.executeUpdate();
			if(status>0&&id!=null) {
				var rs = stmt.getGeneratedKeys();
				if (rs.next())m.put(id,rs.getInt(1));
				Io.close(rs);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			Io.close(conn);
		}
		return m;
	}
	public Object insert(Collection<?> c){//批量插入
		return c;
	}
	public Object mager(Object o) {
		var m=toMap(o);
		var idName=m.get("+id");
		Object pk="id";
		if(idName instanceof String n&&Strings.isNotEmpty(n))pk=n;
		if(Lang.isEmpty(m.get(pk))) {
			m.remove(pk);//删除空id
			return insert(m);
		}else {
			return update(m);
		}
	}
	
	private Map toMap(Object o) {
		Map m=null;
		if(o instanceof Map m0)m=m0;
		else{
			m=Json.jsonObject(o);
			if(!m.containsKey("table")) {
				var tab=o.getClass().getAnnotation(Table.class);
				if(tab!=null) {
					m.put("table",tab.value().isEmpty()?o.getClass().getSimpleName():tab.value());
					if(tab.auto()) {
						m.put("+id",tab.pk());
					}
				}else {
					m.put("table",o.getClass().getSimpleName());
				}
			}
		}
		return m;
	}
	
	public Object update(Object o) {
//		return new Sql().mager(o).execute();
		return null;
	}
	public int update(String table,Object o,Cnd cnd) {
		StringBuilder sb=new StringBuilder("update ");
		sb.append(table).append(" set ");
		var m=toMap(o);
		try {m.remove("table");m.remove("+id");}catch(UnsupportedOperationException e){}//忽略
		m.forEach((k,v)->{sb.append('`').append(k).append("` = ?,");});
		sb.deleteCharAt(sb.length()-1).append(cnd);
		cnd.insertParam(m.values());
		return executeUpdate(sb.toString(),cnd.getParams().toArray());
	}
	public int update(String sql,Object...o) {return executeUpdate(sql,o);}

	public long count(String sql,Object...args) {return fun("count","*",sql,Long.class,args);}
	
	public boolean delete(String sql,Object cnd){
		if(!sql.trim().toLowerCase().startsWith("delete ")) {
			sql="delete from "+sql;//sql 是表名
		}
		if(cnd instanceof Cnd c) {
			return update(sql+c,c.getParams().toArray())>0;
		}
		return update(sql,cnd)>0;
	}
	
	/**
	 * SQL函数计算
	 * @param fun AVG,COUNT,MAX,MIN,SUM
	 * @param name (表达式) 如*
	 * @param sql 表名 如包含sql则忽略
	 * @param t<T> 返回类型
	 * @param args sql参数 cnd则忽略其他参数
	 * @return
	 */
	public <T> T fun(String fun,String name,String sql,Class<T> t,Object...args) {
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			sql="select "+fun+"("+name+") from "+sql;//sql 是表名
		}
		if(args!=null&&args[0] instanceof Cnd c) {
			c.notcount(false);//是统计对limit
			var o=queryForObject(t,sql+c.toString(),c.getParams().toArray());
			c.notcount(true);
			return o;
		}
		return queryForObject(t,sql,args);
	}
	/**
	 * TODO 批量更新 暂时未测试
	 * @param sql INSERT INTO test VALUES(?, ?, ?, ?, ?)
	 * @param args
	 * @return
	 */
	public int[] batch(String sql,List<Object[]> args) {
		var conn=getConnection();
		StringBuilder sb=new StringBuilder("batch sql:");
		try {
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn.prepareStatement(sql);
			for (int i = 0; i < args.size(); i++) {
				Jdbc.setParameters(stmt,args.get(i));stmt.addBatch();
				if(Jdbc.showSql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
					sb.append("\n").append(stmt.toString().substring(stmt.getClass().getName().length()+2));
				}
			}
			int[] cs = stmt.executeBatch();
			conn.commit();
			sb.append("\nbatch sql ok:").append(cs.length);
			if(Jdbc.showSql)Jdbc.log.debug(sb.toString());
			return cs;
		}catch(BatchUpdateException b) {
			int[] cs = b.getUpdateCounts();
			sb.append("\nbatch sql err:[");
			for (int i = 0; i < cs.length; i++) {
			  sb.append(cs[i]).append(",");
			}
			sb.deleteCharAt(sb.length()-1);
			Jdbc.log.warn(sb.append("] ").append(b.getMessage()).toString());
			try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}//回滚
		}
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Io.close(conn);
		}
		return null;
	}
	
	public int executeUpdate(String sql,Object...args) {
		try(var conn=getConnection();var stmt=conn.prepareStatement(sql);){
			if(sql.indexOf('?')!=-1)Jdbc.setParameters(stmt,args);
			if(Jdbc.showSql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("executeUpdate"+stmt.toString().substring(stmt.getClass().getName().length()));
			}
			return stmt.executeUpdate();
		}catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
     * 判断表是否存在
     *
     * @param tableName  表名
     * @param dbType     数据库雷西看那个
     * @return boolean
     */
    public boolean exists(String tableName) {
        try {
            var rset = getConnection().getMetaData().getTables(null, null,tableName.toUpperCase(), null);
            return rset.next();
        } catch (SQLException e) {
        	return false;
        }
    }
    public int drop(String tableName) {
    	return executeUpdate("drop table "+tableName);
    }
    public ResultSet queryForStream(String sql){
    	var conn=getConnection();
    	try(var stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);){
    		stmt.setFetchSize(Integer.MIN_VALUE);
    		var rs = stmt.executeQuery(sql);
    		return rs;
		} catch (Exception e) {
			// TODO: handle exception
		}
		return null;
    }
	
}
